




ALTER   function fn_get_process_flow_backward_by_id (@process_step_id bigint)
--create proc fn_get_process_flow_backward_by_id (@process_step_id bigint)
returns  @fn_result Table(Temp_Count int,
			ProcessStep_ID bigint, 
			PS_Type varchar(20), 
			Process_Type_Name varchar(20))

As
begin
	declare @temp_process_step_id bigint
	declare @temp_ps_type varchar(20)
	declare @temp_ps_type_name varchar(20)
	declare @flag_step_id bigint
	declare @row_count int	
	declare @result Table(Temp_Count int, 
			ProcessStep_ID bigint, 
			PS_Type varchar(20), 
			Process_Type_Name varchar(20))	
	set @row_count = 1

	select @temp_ps_type=PS_Type, @temp_ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @process_step_id
	insert into @result(Temp_Count, ProcessStep_ID, PS_Type, Process_Type_Name) values(@row_count, @process_step_id, @temp_ps_type, @temp_ps_type_name)
	select @temp_process_step_id = ProcessStep_ID from Process_Step_Traverse_Table where Next_ProcessStep_ID = @process_step_id

	set @row_count = @row_count+1
	while @temp_process_step_id <> 0
	begin
		set @flag_step_id = 0
		select @temp_ps_type=PS_Type, @temp_ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
		insert into @result(Temp_Count, ProcessStep_ID, PS_Type, Process_Type_Name) values(@row_count, @temp_process_step_id, @temp_ps_type, @temp_ps_type_name)
		select @flag_step_id = ProcessStep_ID from Process_Step_Traverse_Table where Next_ProcessStep_ID = @temp_process_step_id
		set @temp_process_step_id = @flag_step_id
		set @row_count = @row_count+1
	end
	
	insert @fn_result select *from @result
	return
end 



